package com.neusoft.common.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.neusoft.common.util.Pagenation;


/**
 * 2009-12-02: 添加了泛型和可变参数支持
 */
public class BaseDAO{
    public static int BATCH_SIZE = 40;

    protected JdbcTemplate jdbcTemplate;
    
    protected JdbcTemplate jdbcTemplate_TRSWCMV65;

    public JdbcTemplate getJdbcTemplate(){
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }

    public int[] batchUpdate(JdbcTemplate jdbcTemplates,String[] sqls){
        return jdbcTemplate.batchUpdate(sqls);
    }

    /**
     * 查询记录结果为map list
     *
     * @param sql
     *
     * @return
     */
    public List<Map<String, Object>> queryForList(JdbcTemplate jdbcTemplate,String sql){
        return jdbcTemplate.query(sql, new EncodingColumnMapRowMapper());
    }

    /**
     * 查询记录结果为map list
     *
     * @param sql
     * @param args
     *
     * @return
     */
    public List<Map<String, Object>> queryForList(JdbcTemplate jdbcTemplate,String sql, Object... args){
        //args = EncodingConvert.convertToGBK(args);
        return jdbcTemplate.query(sql,
                                  new EncodingPreparedStatementSetter(args),
                                  new EncodingColumnMapRowMapper());
    }

    /**
     * 查询返回一条记录的查询结果
     *
     * @param sql
     * @param type 查询后的结果转换后的类型
     *
     * @return
     */
    public <T> T queryForObject(JdbcTemplate jdbcTemplate,String sql, Class<T> type){
        return (T) jdbcTemplate.queryForObject(sql, type);
    }

    /**
     * 查询返回一条记录的查询结果
     *
     * @param sql
     * @param args
     * @param type 查询后的结果转换后的类型
     *
     * @return
     */
    public <T> T queryForObject(JdbcTemplate jdbcTemplate,String sql, Object[] args, Class<T> type){
        return (T) jdbcTemplate.queryForObject(sql, args, type);
    }

    /**
     * 查询整数
     *
     * @param sql
     *
     * @return
     */
    public int queryForInt(JdbcTemplate jdbcTemplate,String sql){
        return jdbcTemplate.queryForInt(sql);
    }

    /**
     * 查询整数
     *
     * @param sql
     * @param args
     *
     * @return
     */
    public int queryForInt(JdbcTemplate jdbcTemplate,String sql, Object... args){
        //args = EncodingConvert.convertToGBK(args);
        return jdbcTemplate.queryForInt(sql, args);
    }

    /**
     * 返回单条记录
     *
     * @param sql
     *
     * @return
     */
    public Map<String, Object> queryForMap(JdbcTemplate jdbcTemplate,String sql){
        return (Map<String, Object>) jdbcTemplate.queryForObject(sql, new EncodingColumnMapRowMapper());
    }

    public Map<String, Object> queryForMap(JdbcTemplate jdbcTemplate,String sql, Object... args){
        //	args = EncodingConvert.convertToGBK(args);
        return (Map) jdbcTemplate.queryForObject(sql, args, new EncodingColumnMapRowMapper());
    }

    /**
     * 执行更新记录
     *
     * @param sql
     *
     * @return
     */
    public int update(JdbcTemplate jdbcTemplate,String sql){
        return jdbcTemplate.update(sql);
    }

    /**
     * 执行更新记录
     *
     * @param sql
     * @param args
     *
     * @return
     */
    public int update(JdbcTemplate jdbcTemplate,String sql, Object... args){
        //args = EncodingConvert.convertToGBK(args);
        return jdbcTemplate.update(sql, new EncodingPreparedStatementSetter(args));
    }


    /**
     * 查询List（Convert Bean）
     *
     * @param sql
     * @param type
     *
     * @return
     */
    public <T> List<T> queryForBeanList(JdbcTemplate jdbcTemplate,String sql, Class<T> type){
        return jdbcTemplate.query(sql, new BeanRowMapper(type));
    }

    /**
     * 查询List（Convert Bean）
     *
     * @param sql
     * @param args
     * @param type
     *
     * @return
     */
    public <T> List<T> queryForBeanList(JdbcTemplate jdbcTemplate,String sql, Object[] args, Class<T> type){
        //	args = EncodingConvert.convertToGBK(args);
        return jdbcTemplate.query(sql, args, new BeanRowMapper(type));
    }

    /**
     * 返回Bean 根据rs结果转换成bean
     *
     * @param sql
     * @param type
     *
     * @return
     */
    public <T> T queryForBean(JdbcTemplate jdbcTemplate,String sql, Class<T> type){
        return (T) jdbcTemplate.queryForObject(sql, new BeanRowMapper(type));
    }

    /**
     * 返回Bean 根据rs结果转换成bean
     *
     * @param sql
     * @param args
     * @param type
     *
     * @return
     */
    public <T> T queryForBean(JdbcTemplate jdbcTemplate,String sql, Object[] args, Class<T> type){
        //args = EncodingConvert.convertToGBK(args);

        Object object = null;
        try{
            object = jdbcTemplate
                    .queryForObject(sql, args, new BeanRowMapper(type));
        } catch(Exception e){
            try{
                object = type.newInstance();
            } catch(InstantiationException e1){
                e1.printStackTrace();
            } catch(IllegalAccessException e1){
                e1.printStackTrace();
            }
        }
        return (T) object;
    }

    /**
     * 分页 数据转换为bean
     *
     * @param sql
     * @param sqlCount
     * @param page
     * @param type
     *
     * @return
     */
    public <T> List<T> queryForBeanPage(JdbcTemplate jdbcTemplate,String sql, String sqlCount, Pagenation page,
            Class<T> type){
        int recordCount = jdbcTemplate.queryForInt(sqlCount);
        page.setRecordCount(recordCount);
        if(recordCount == 0 || recordCount < page.getRowBegin())
            return Pagenation.emptyList();
        String pageSQL = createPageSQL(jdbcTemplate,sql, page);
        List list = this.queryForBeanList(jdbcTemplate,pageSQL, type);
        page.setItems(list);
        return list;
    }

    public String createPageSQL(JdbcTemplate jdbcTemplate,String sql, Pagenation page){
        int begin = page.getRowBegin();
        int end = page.getRowEnd();
        //MYSQL
        sql = sql + " limit " + (begin) + " , " +page.getPageSize();
        
        
        // sql = sql.toLowerCase();

         //ORACLE
//        sql = "select * from " + "( select row_.*, rownum rownum_ from " + "("
//                + sql + " ) row_ where rownum < " + end + ") where rownum_ >= "
//                + begin;
        
        
        
        // sql = sql.replaceAll("from", " ,rownum mynum from");
        // sql = "SELECT * FROM (" + sql + ") where mynum>=" + begin
        // + " and mynum<" + end;
        return sql;

    }

    /**
     * 分页 数据转换为bean
     *
     * @param sql
     * @param sqlCount
     * @param args
     * @param page
     * @param type
     *
     * @return
     */
    public <T> List<T> queryForBeanPage(JdbcTemplate jdbcTemplate,String sql, String sqlCount, Object[] args,
            Pagenation page, Class<T> type){
        int recordCount = queryForInt(jdbcTemplate,sqlCount, args);
        page.setRecordCount(recordCount);
        if(recordCount == 0 || recordCount < page.getRowBegin())
            return Pagenation.emptyList();
        String pageSQL = createPageSQL(jdbcTemplate,sql, page);
        List list = this.queryForBeanList(jdbcTemplate,pageSQL, args, type);
        page.setItems(list);
        return list;
    }

    public <T> List<T> findForBeanPage(JdbcTemplate jdbcTemplate,String sql, String sqlCount, Object[] args,
            Pagenation page, Class<T> type, int key){
//        args = EncodingConvert.convertToGBK(args);
        args[key] = "%" + args[key].toString() + "%";
        int recordCount = this.jdbcTemplate.queryForInt(sqlCount, args);
        page.setRecordCount(recordCount);
        if(recordCount == 0 || recordCount < page.getRowBegin())
            return Pagenation.emptyList();

        String pageSQL = createPageSQL(jdbcTemplate,sql, page);
        List list = this.queryForBeanList(jdbcTemplate,pageSQL, args, type);
        page.setItems(list);
        return list;
    }

    /**
     * 分页查询数据
     *
     * @param sql
     * @param sqlCount
     * @param page
     *
     * @return
     */
    public List<Map<String, Object>> queryForPage(JdbcTemplate jdbcTemplate,String sql, String sqlCount, Pagenation page){
        int recordCount = jdbcTemplate.queryForInt(sqlCount);
        page.setRecordCount(recordCount);
        if(recordCount == 0 || recordCount < page.getRowBegin())
            return Pagenation.emptyList();

        String pageSQL = createPageSQL(jdbcTemplate,sql, page);
        List list = this.queryForList(jdbcTemplate,pageSQL);
        page.setItems(list);
        return list;
    }

    /**
     * 分页查询数据
     *
     * @param sql
     * @param sqlCount
     * @param args
     * @param page
     *
     * @return
     */
    public List<Map<String, Object>> queryForPage(JdbcTemplate jdbcTemplate,String sql, String sqlCount, Object[] args,
            Pagenation page){
        int recordCount = queryForInt(jdbcTemplate,sqlCount, args);
        page.setRecordCount(recordCount);
        if(recordCount == 0 || recordCount < page.getRowBegin())
            return Pagenation.emptyList();

        String pageSQL = createPageSQL(jdbcTemplate,sql, page);
        List list = this.queryForList(jdbcTemplate,pageSQL, args);
        page.setItems(list);
        return list;
    }

    /**
     * 更新数据
     *
     * @param tableName 表名
     * @param parms     参数
     * @param key       where字段名
     *
     * @return
     */
    @SuppressWarnings("unchecked")
    public int updateByMap(JdbcTemplate jdbcTemplate,String tableName, Map<String, Object> parms, String key){
        if(parms == null || parms.isEmpty())
            return 0;

        if(tableName == null || tableName.equals("")){
            return 0;
        }

        // 如果有where条件
        Object keyValue = null;
        if(key != null && !key.equals("")){
//            keyValue = EncodingConvert.convertToGBK(parms.get(key));
            parms.remove(key);
        }

        String sql = "update " + tableName + " set ";
        List list = new ArrayList();
        Iterator it = parms.entrySet().iterator();
        for(int i = 0; it.hasNext(); i++){
            Map.Entry entry = (Map.Entry) it.next();
            //	String tkey = EncodingConvert.convertToGBK((String) entry.getKey());
            String tkey = (String) entry.getKey();
            if(i == 0){
                sql = sql + tkey + "=? ";
            } else{
                sql = sql + ", " + tkey + "=? ";
            }

            Object tvalue = entry.getValue();
            list.add(tvalue);
        }
        // 如果有where条件
        if(key != null && !key.equals("")){
            sql = sql + " where " + key + "= ?";
            list.add(keyValue);
        }
        Object[] args = list.toArray();

        return jdbcTemplate.update(sql, args);

    }

    /**
     * 插入数据
     *
     * @param tableName 表名
     * @param parms     参数
     *
     * @return
     */
    @SuppressWarnings("unchecked")
    public int insertByMap(JdbcTemplate jdbcTemplate,String tableName, Map<String, Object> parms){
        if(parms == null || parms.isEmpty())
            return 0;

        if(tableName == null || tableName.equals("")){
            return 0;
        }

        String sql = "insert into  " + tableName + " ( ";
        String tmp = "";
        List list = new ArrayList();
        Iterator it = parms.entrySet().iterator();

        for(int i = 0; it.hasNext(); i++){
            Map.Entry entry = (Map.Entry) it.next();
            String tkey = (String) entry.getKey();

            if(i == 0){
                sql = sql + tkey;
                tmp = tmp + "?";
            } else{
                sql = sql + ", " + tkey;
                tmp = tmp + ",?";
            }

            //Object tvalue = EncodingConvert.convertToGBK(entry.getValue());
            Object tvalue = entry.getValue();
            list.add(tvalue);
        }
        sql = sql + ") values (" + tmp + ")";

        Object[] args = list.toArray();

        return jdbcTemplate.update(sql, args);

    }

    /**
     * 带事务的批处理sql
     *
     * @param sqls
     *
     * @return
     *
     * @author hunk
     */
    public boolean batchUpdate(JdbcTemplate jdbcTemplate,List<String> sqls){
        Statement st = null;
        Connection conn = null;
        int i = 0;
        try{

            conn = jdbcTemplate.getDataSource().getConnection();
            if(conn == null && conn.isClosed()){
                System.out.println("连接已关闭！");
                return false;
            }
            conn.setAutoCommit(false);
            st = conn.createStatement();
            for(i = 0; i < sqls.size(); i++){
//    			st.executeUpdate(sql.elementAt(i).toString());
                st.executeUpdate(sqls.get(i));
            }
            st.close();
            conn.commit();
            conn.setAutoCommit(true);
            conn.close();
            return true;
        } catch(SQLException e){
            System.out.println(e.getMessage());
            System.out.println(" 操作失败:" + sqls.get(i));
            System.out.println(" 事务中所有sql为：");
            for(int j = 0; j < sqls.size(); j++)
                System.out.println("\t" + sqls.get(j));
            try{
                conn.rollback();//回滚事务
                conn.setAutoCommit(true);
                conn.close();
            } catch(SQLException e1){
                e1.printStackTrace();
            }
            return false;
        }
    }

	public JdbcTemplate getJdbcTemplate_TRSWCMV65()
	{
		return jdbcTemplate_TRSWCMV65;
	}

	public void setJdbcTemplate_TRSWCMV65(JdbcTemplate jdbcTemplate_TRSWCMV65)
	{
		this.jdbcTemplate_TRSWCMV65 = jdbcTemplate_TRSWCMV65;
	}
}
